﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonSweepstakes;
using System.Data;

namespace DatSorteos
{

    /// <summary>
    /// Capa de Datos. Tercera capa. Se encarga de la comunicacion con la DB 
    /// y el formateo de los mismos para la inclusion de los mismos en los
    /// Datasets (Que estaran en Common) encargados del manejar el contenido retirado.
    /// </summary>
    public class DatSweepstakes
    {


        #region Constructor / Destructor
        public DatSweepstakes(string ConnQuery)
        {
            SqlConnect(ConnQuery);
            //conn.ConnectionString = ConnQuery;
        }

        ~DatSweepstakes()
        {
            if (conn.State != ConnectionState.Closed)
            {
                try
                {
                    conn.Close();
                }
                catch (Exception)
                {
                    //TODO: Loggear.
                }
            }
        }
        #endregion

        #region Private Properties
        //private OleDbConnection conn = new OleDbConnection();
        private SqlConnection conn = new SqlConnection();


        #endregion

        #region Public Properties
        public enum GameType
        {
            Bingo = 1,
            Sweepstake = 2,
            ThreeCard = 3,
            FourCard = 4
        }

        public Util ComUtil = new Util();
        public Sweepstakes DSSweepstakes = new Sweepstakes();
        #endregion

        #region Private Methods
        private void SqlConnect(string ConnQuery)
        {
            //throw new InvalidOperationException("El metodo SqlDbConnect aun no fue implementado.");
            conn.ConnectionString = ConnQuery;
            conn.Open();
        }

        #region Inserts
        private void InsertGame(Sweepstakes.GameListRow gr)
        {
            SqlCommand comm = new SqlCommand(InsertGameQuery(gr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        private void InsertBingoCard(Sweepstakes.SweepstakeBingoRow br)
        {
            SqlCommand comm = new SqlCommand(InsertBingoCardQuery(br), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        private void InsertFourCard(Sweepstakes.SweepstakeFourRow fr)
        {
            SqlCommand comm = new SqlCommand(InsertFourCardQuery(fr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        private void InsertThreeCard(Sweepstakes.SweepstakeThreeRow tr)
        {
            SqlCommand comm = new SqlCommand(InsertThreeCardQuery(tr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        private void InsertSweepstakeCard(Sweepstakes.SweepstakeRow sr)
        {
            SqlCommand comm = new SqlCommand(InsertSweepstakeQuery(sr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        #region Insert Queries

        private string InsertGameQuery(Sweepstakes.GameListRow gr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("INSERT INTO GameList");
            sbQ.Append("             (Name, Used, Type, IdFrom, IdTo, Expires)");
            sbQ.Append("VALUES        ('");
            sbQ.Append(gr.Name);
            sbQ.Append("', 0, ");
            sbQ.Append(gr.IdTypeFk);
            sbQ.Append(",");
            sbQ.Append(gr.IdFrom);
            sbQ.Append(",");
            sbQ.Append(gr.IdTo);
            sbQ.Append(",CONVERT(DATETIME, '");
            sbQ.Append(gr.Expires.ToShortDateString());
            sbQ.Append("', 102))");
            return sbQ.ToString();
        }

        private string InsertFourCardQuery(Sweepstakes.SweepstakeFourRow fr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("INSERT INTO Four");
            sbQ.Append("             (UserId, Val1, Val2, Val3, Val4, Used, IdGameFk)");
            sbQ.Append("VALUES        (");
            sbQ.Append(fr.Id);
            sbQ.Append(",");
            //No es cadena asi que no se empieza y termina con '
            sbQ.Append(fr.Val1);
            sbQ.Append(",");
            sbQ.Append(fr.Val2);
            sbQ.Append(",");
            sbQ.Append(fr.Val3);
            sbQ.Append(",");
            sbQ.Append(fr.Val4);
            ///Aun el carton no fue usado asi que la bandera de uso se inicializa en 0
            sbQ.Append(", 0, ");
            sbQ.Append(fr.IdGameFk);
            sbQ.Append(")");
            return sbQ.ToString();
        }

        private string InsertSweepstakeQuery(Sweepstakes.SweepstakeRow sr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("INSERT INTO Sweepstake");
            sbQ.Append("             (UserId, Used, IdGameFk)");
            sbQ.Append("VALUES        (");
            sbQ.Append(sr.Id);
            sbQ.Append(", 0, ");
            sbQ.Append(sr.IdGameFk);
            return sbQ.ToString();
        }

        private string InsertThreeCardQuery(Sweepstakes.SweepstakeThreeRow tr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("INSERT INTO Three");
            sbQ.Append("             (UserId, Val1, Val2, Val3, Used, IdGameFk)");
            sbQ.Append("VALUES        (");
            sbQ.Append(tr.Id);
            sbQ.Append(",");
            //No es cadena asi que no se empieza y termina con '
            sbQ.Append(tr.Val1);
            sbQ.Append(",");
            sbQ.Append(tr.Val2);
            sbQ.Append(",");
            sbQ.Append(tr.Val3);
            ///Aun el carton no fue usado asi que la bandera de uso se inicializa en 0
            sbQ.Append(", 0, ");
            sbQ.Append(tr.IdGameFk);
            sbQ.Append(")");
            return sbQ.ToString();
        }

        private string InsertBingoCardQuery(Sweepstakes.SweepstakeBingoRow br)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("INSERT INTO Bingo");
            sbQ.Append("             (UserId, Row1, Row2, Row3, UsedBingo, UsedRow, IdGameFk)");
            sbQ.Append("VALUES        (");
            sbQ.Append(br.Id);
            sbQ.Append(",'");
            //Es cadena asi que se empieza y termina con '
            sbQ.Append(br.Row1);
            sbQ.Append("','");
            sbQ.Append(br.Row2);
            sbQ.Append("','");
            sbQ.Append(br.Row3);
            ///Aun el carton no fue usado asi que las banderas de uso se inicializan en 0
            sbQ.Append("', 0, 0, ");
            sbQ.Append(br.IdGameFk);
            sbQ.Append(")");
            return sbQ.ToString();
        }
        #endregion
        #endregion

        #region Deletes
        private void DeleteBingoCard(Sweepstakes.SweepstakeBingoRow br)
        {
            SqlCommand comm = new SqlCommand(DeleteBingoCardQuery(br), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        private void DeleteFourCard(Sweepstakes.SweepstakeFourRow fr)
        {
            SqlCommand comm = new SqlCommand(DeleteFourCardQuery(fr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }
        private void DeleteThreeCard(Sweepstakes.SweepstakeThreeRow tr)
        {
            SqlCommand comm = new SqlCommand(DeleteThreeCardQuery(tr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        private void DeleteSweepstakeCard(Sweepstakes.SweepstakeRow sr)
        {
            SqlCommand comm = new SqlCommand(DeleteSweepstakeCardQuery(sr), conn);
            comm.CommandType = CommandType.Text;
            comm.ExecuteNonQuery();
        }

        #region Delete Queries
        private string DeleteBingoCardQuery(Sweepstakes.SweepstakeBingoRow br)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("DELETE FROM Bingo");
            sbQ.Append("             WHERE UserId = ");
            sbQ.Append(br.Id);
            return sbQ.ToString();
        }

        private string DeleteFourCardQuery(Sweepstakes.SweepstakeFourRow fr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("DELETE FROM Four");
            sbQ.Append("             WHERE UserId = ");
            sbQ.Append(fr.Id);
            return sbQ.ToString();
        }

        private string DeleteThreeCardQuery(Sweepstakes.SweepstakeThreeRow tr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("DELETE FROM Three");
            sbQ.Append("             WHERE UserId = ");
            sbQ.Append(tr.Id);
            return sbQ.ToString();
        }

        private string DeleteSweepstakeCardQuery(Sweepstakes.SweepstakeRow sr)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("DELETE FROM Sweepstake");
            sbQ.Append("             WHERE UserId = ");
            sbQ.Append(sr.Id);
            return sbQ.ToString();
        }
        #endregion
        #endregion
        
        #region Getter Queries
        /// <summary>
        /// GetGameQuery Id, Name, Used, Type, IdFrom, IdTo, Expires
        /// </summary>
        /// <param name="Id">Puede ser 0, que significa sin filtrar.</param>
        /// <returns></returns>
        private string GetGameQuery(int Id)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("SELECT        Id, Name, Used, Type, IdFrom, IdTo, Expires ");
            sbQ.Append("FROM            GameList WHERE Id = ");
            sbQ.Append(Id);
            sbQ.Append(" OR 0 = ");
            sbQ.Append(Id);
            return sbQ.ToString();
        }

        private string GetBingoDataQuery(int IdGameFk)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("SELECT        UserId, Row1, Row2, Row3, UsedRow, UsedBingo ");
            sbQ.Append("FROM            Bingo WHERE IdGameFk = ");
            sbQ.Append(IdGameFk);
            return sbQ.ToString();
        }

        private string GetSweepstakeDataQuery(int IdGameFk)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("SELECT        UserId, Used");
            sbQ.Append("FROM            Sweepstake WHERE IdGameFk = ");
            sbQ.Append(IdGameFk);
            return sbQ.ToString();
        }

        private string GetThreeDataQuery(int IdGameFk)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("SELECT        UserId, Val1, Val2, Val3, Used ");
            sbQ.Append("FROM            Four WHERE IdGameFk = ");
            sbQ.Append(IdGameFk);
            return sbQ.ToString();
        }

        private string GetFourDataQuery(int IdGameFk)
        {
            StringBuilder sbQ = new StringBuilder();
            sbQ.Append("SELECT        UserId, Val1, Val2, Val3, Val4, Used ");
            sbQ.Append("FROM            Four WHERE IdGameFk = ");
            sbQ.Append(IdGameFk);
            return sbQ.ToString();
        }

        #endregion
       

        #region Public Methods
        ///Aqui los metodos que haran de intermediarios con 
        ///la capa Business para el manejo de datos.

        public void Insert(Sweepstakes dsSwi)
        {
            ///Se tienen que hacer inserts multiples dependiendo de GameType
            ///TODO: Begin Transaction End Transaction
            InsertGame(dsSwi.GameList[0]);
            switch ((GameType)dsSwi.GameList[0].IdTypeFk)
            {
                case GameType.Bingo:
                    dsSwi.SweepstakeBingo.ToList().ForEach(InsertBingoCard);
                    break;
                case GameType.Sweepstake:
                    dsSwi.Sweepstake.ToList().ForEach(InsertSweepstakeCard);
                    break;
                case GameType.ThreeCard:
                    dsSwi.SweepstakeThree.ToList().ForEach(InsertThreeCard);
                    break;
                case GameType.FourCard:
                    dsSwi.SweepstakeFour.ToList().ForEach(InsertFourCard);
                    break;
                default:
                    throw new NotSupportedException(
                        "Este tipo de formato que Ud. pide aun no se lo banca el programa pedorro este.");
            }
        }
        
        public void Update(Sweepstakes dsSwi)
        {
            GetGameData(dsSwi.GameList[0]);
            
            Sweepstakes.SweepstakeBingoDataTable prevBingo = DSSweepstakes.SweepstakeBingo;
            dsSwi.SweepstakeBingo.Where(br => prevBingo.All(pbr => br.Id != pbr.Id)).ToList().ForEach(InsertBingoCard);
            prevBingo.Where(pbr => dsSwi.SweepstakeBingo.All(br => pbr.Id != br.Id)).ToList().ForEach(DeleteBingoCard);

            Sweepstakes.SweepstakeThreeDataTable prevThree = DSSweepstakes.SweepstakeThree;
            dsSwi.SweepstakeThree.Where(tr => prevThree.All(ptr => tr.Id != ptr.Id)).ToList().ForEach(InsertThreeCard);
            prevThree.Where(ptr => dsSwi.SweepstakeThree.All(tr => ptr.Id != tr.Id)).ToList().ForEach(DeleteThreeCard);

            Sweepstakes.SweepstakeFourDataTable prevFour = DSSweepstakes.SweepstakeFour;
            dsSwi.SweepstakeFour.Where(fr => prevFour.All(pfr => fr.Id != pfr.Id)).ToList().ForEach(InsertFourCard);
            prevFour.Where(pfr => dsSwi.SweepstakeFour.All(fr => pfr.Id != fr.Id)).ToList().ForEach(DeleteFourCard);

            Sweepstakes.SweepstakeDataTable prev = DSSweepstakes.Sweepstake;
            dsSwi.Sweepstake.Where(sr => prev.All(psr => sr.Id != psr.Id)).ToList().ForEach(InsertSweepstakeCard);
            prev.Where(psr => dsSwi.Sweepstake.All(sr => psr.Id != sr.Id)).ToList().ForEach(DeleteSweepstakeCard);
        }

      

        /// <summary>
        /// Trae todos los juegos, pero no sus cartones.
        /// </summary>
        public void GetGames()
        {
            DSSweepstakes = new Sweepstakes();
            SqlCommand comm = new SqlCommand(GetGameQuery(0), conn);
            SqlDataReader dr = comm.ExecuteReader();
            #region GameList DataTable Fill
            while (dr.Read())
            {
                Sweepstakes.GameListRow gRow = DSSweepstakes.GameList.NewGameListRow();
                gRow.Id = dr.GetInt32(0);
                gRow.Name = dr.GetString(1);
                gRow.Used = dr.GetInt32(2);
                gRow.IdTypeFk = dr.GetInt32(3);
                gRow.IdFrom = dr.GetInt32(4);
                gRow.IdTo = dr.GetInt32(5);
                gRow.Expires = dr.GetDateTime(6);
                DSSweepstakes.GameList.AddGameListRow(gRow);
            }
            dr.Close();
            #endregion
        }

        public void GetGameData(Sweepstakes.GameListRow gr)
        {
            string myQuery = string.Empty;
            switch ((GameType)gr.IdTypeFk)
            {
                case GameType.Bingo:
                    myQuery = GetBingoDataQuery(gr.Id);
                    break;
                case GameType.Sweepstake:
                    myQuery = GetSweepstakeDataQuery(gr.Id);
                    break;
                case GameType.ThreeCard:
                    myQuery = GetThreeDataQuery(gr.Id);
                    break;
                case GameType.FourCard:
                    myQuery = GetFourDataQuery(gr.Id);
                    break;
                default:
                    throw new NotSupportedException("El programa pedorro este no soporta otro tipo de juego que no sean los listados");
                    
            }
            SqlCommand comm = new SqlCommand(myQuery, conn);
            SqlDataReader dr = comm.ExecuteReader();
            switch ((GameType)gr.IdTypeFk)
            {
                case GameType.Bingo:
                    DSSweepstakes.SweepstakeBingo.Clear();
                    while (dr.Read())
                    {
                        Sweepstakes.SweepstakeBingoRow br = DSSweepstakes.SweepstakeBingo.NewSweepstakeBingoRow();
                        br.Id = dr.GetInt32(0);
                        br.IdGameFk = gr.IdTypeFk;
                        br.Row1 = dr.GetString(1);
                        br.Row2 = dr.GetString(2);
                        br.Row3 = dr.GetString(3);
                        br.UsedRow = dr.GetBoolean(4);
                        br.UsedBingo = dr.GetBoolean(5);
                        DSSweepstakes.SweepstakeBingo.AddSweepstakeBingoRow(br);
                    }
                    break;
                case GameType.Sweepstake:
                    DSSweepstakes.Sweepstake.Clear();
                    while (dr.Read())
                    {
                        Sweepstakes.SweepstakeRow sr = DSSweepstakes.Sweepstake.NewSweepstakeRow();
                        sr.Id = dr.GetInt32(0);
                        sr.IdGameFk = gr.IdTypeFk;
                        sr.Used = dr.GetBoolean(1);
                        DSSweepstakes.Sweepstake.AddSweepstakeRow(sr);
                    }
                    break;
                case GameType.ThreeCard:
                    DSSweepstakes.SweepstakeThree.Clear();
                    while (dr.Read())
                    {
                        Sweepstakes.SweepstakeThreeRow tr = DSSweepstakes.SweepstakeThree.NewSweepstakeThreeRow();
                        tr.Id = dr.GetInt32(0);
                        tr.IdGameFk = gr.IdTypeFk;
                        tr.Val1 = dr.GetInt32(1);
                        tr.Val2 = dr.GetInt32(2);
                        tr.Val3 = dr.GetInt32(3);
                        tr.Used = dr.GetBoolean(4);
                        DSSweepstakes.SweepstakeThree.AddSweepstakeThreeRow(tr);
                    }
                    break;
                case GameType.FourCard:
                    DSSweepstakes.SweepstakeFour.Clear();
                    while (dr.Read())
                    {
                        Sweepstakes.SweepstakeFourRow fr = DSSweepstakes.SweepstakeFour.NewSweepstakeFourRow();
                        fr.Id = dr.GetInt32(0);
                        fr.IdGameFk = gr.IdTypeFk;
                        fr.Val1 = dr.GetInt32(1);
                        fr.Val2 = dr.GetInt32(2);
                        fr.Val3 = dr.GetInt32(3);
                        fr.Val4 = dr.GetInt32(4);
                        fr.Used = dr.GetBoolean(5);
                        DSSweepstakes.SweepstakeFour.AddSweepstakeFourRow(fr);
                    }
                    break;
            }
            dr.Close();
            #endregion
        }
        
        /*

        private void GetDataByClientArray(List<int> clientArray)
        {
            GetData();

            foreach (Client.PersonasRow prow in DSClient.Personas.Rows)
            {
                if (clientArray.BinarySearch(prow.ClienteId) < 0)
                {
                    prow.Delete();
                }
            }
            foreach (Client.DireccionesRow drow in DSClient.Direcciones.Rows)
            {
                if (clientArray.BinarySearch(drow.ClienteFk) < 0)
                {
                    drow.Delete();
                }
            }
            foreach (Client.TelefonosRow trow in DSClient.Telefonos.Rows)
            {
                if (clientArray.BinarySearch(trow.ClienteFk) < 0)
                {
                    trow.Delete();
                }
            }
        }
        */
        #endregion

    }
}
